#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
create database IF NOT EXISTS edu_das;

-- -----------需求1:每天/每月/每年线上线下以及新老学员的意向用户个数
DROP TABLE IF EXISTS edu_das.all_table;
create table edu_das.all_table(
    year_date                               string  ,
    month_date                              string ,
    day_date                                string,
    origin_type                             string      comment '线上线下' ,
    clue_state                              string      comment '新老学员',
    customer_relationship_amount bigint     comment '意向用户个数',
    group_type_new                          string      comment '维度分组',
    dt_group_type                           string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');


-- - 每天/每月/每年各地区的线上线下以及新老学员的意向用户个数
create table edu_das.area_table(
    year_date                               string  ,
    month_date                              string ,
    day_date                                string,
    area                                    string       comment '地区',
    origin_type                             string      comment '线上线下' ,
    clue_state                              string      comment '新老学员',
    customer_relationship_amount            bigint     comment '意向用户个数',
    group_type_new                          string      comment '维度分组',
    dt_group_type                           string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');


-- - 每天/每月/每年各学科线上线下以及新老学员的意向用户个数Top10
drop table edu_das.subject_table;
create table edu_das.subject_table(
    year_date                               string  ,
    month_date                              string ,
    day_date                                string,
    itcast_subject_id                        integer ,
    subject_name                            string      comment '学科',
    origin_type                             string      comment '线上线下' ,
    clue_state                              string      comment '新老学员',
    customer_relationship_amount            bigint       comment '意向用户个数',
    group_type_new                          string      comment '维度分组',
    dt_group_type                           string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

-- - 每天/每月/每年各校区线上线下以及新老学员的意向用户个数Top10
drop table edu_das.school_table;
create table edu_das.school_table(
    year_date                           string  ,
    month_date                          string ,
    day_date                            string,
    itcast_school_id                    integer ,
    school_name                          string       comment '校区',
    origin_type                          string      comment '线上线下' ,
    clue_state                           string      comment '新老学员',
    customer_relationship_amount         bigint     comment '意向用户个数',
    group_type_new                      string      comment '维度分组',
    dt_group_type                       string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');


-- - 每天/每月/每年各来源渠道线上线下以及新老学员的意向用户个数
create table edu_das.origin_channel_table(
    year_date                   string  ,
    month_date                  string ,
    day_date                    string,
    origin_channel              string      comment '来源渠道',
    origin_type                 string      comment '线上线下' ,
    clue_state                  string      comment '新老学员',
    customer_relationship_amount bigint     comment '意向用户个数',
    group_type_new              string      comment '维度分组',
    dt_group_type               string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

-- - 每天/每月/每年==各咨询中心==线上线下以及新老学员的意向用户个数
create table edu_das.dep_name_table(
    year_date                       string  ,
    month_date                      string ,
    day_date                        string,
    tdepart_id                      int,
    dep_name                        string      comment '销售部门',
    origin_type                     string      comment '线上线下' ,
    clue_state                      string      comment '新老学员',
    customer_relationship_amount    bigint      comment '意向用户个数',
    group_type_new                  string      comment '维度分组',
    dt_group_type                   string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

-- - 每天线上线下及新老学员的有效线索个数
create table edu_das.val_clue_table(
    year_date               string  ,
    month_date              string ,
    day_date                string,
    origin_type             string      comment '线上线下' ,
    clue_state              string      comment '新老学员',
    val_clue_amount         bigint      comment '有效线索的个数',
    group_type_new          string      comment '维度分组',
    dt_group_type           string      comment '时间分组'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');

-- - 每小时线上线下及新老学员的有效线索转化率 = 有效线索个数 / 总线索个数
drop table edu_das.ratio_table;
create table edu_das.ratio_table(
    year_date            string  ,
    month_date          string ,
    day_date            string,
    hour_date           string,
    origin_type         string      comment '线上线下' ,
    clue_state          string      comment '新老学员',
    val_clue_amount     bigint      comment '有效线索的个数',
    clue_amount         bigint      comment '总线索个数',
    group_type_new      string      comment '维度分组',
    dt_group_type       string      comment '时间分组',
    ratio               string     comment '有效线索转化率'
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ('orc.compress'='snappy');